package com.idas.comp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.idas.common.Util;
import com.idas.comp.enums.ResourceType;
import com.idas.comp.enums.Status;
import com.idas.db.DBFields;
import com.idas.db.DBFilter;
import com.idas.wsdl.XmlDocumentation;

public class Module extends Resource {
	@DBFields(field = "code", table = "module")
	@XmlDocumentation(value = "The code of the module")
	private String code;
	@DBFields(field = "session", table = "module")
	@XmlDocumentation(value = "The sesson of the module")
	private String session;

	static {
		DBFilter.registerClass(Module.class);
	}

	public Module() {
	}

	protected Module(ResultSet rs) throws SQLException {
		super(rs);
		code = rs.getString("code");
		session = rs.getString("session");
	}

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}
	
	public void setCode(Connection conn, String code) throws SQLException{
		String sql = "UPDATE module SET code = ? WHERE uuid = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, code);
		ps.setString(2, getUuid());
		ps.execute();
		ps.close();
		setCode(code);
	}

	public String getSession() {
		return session;
	}

	public void setSession(String session) {
		this.session = session;
	}
	
	public void setSession(Connection conn, String session) throws SQLException{
		String sql = "UPDATE module SET session = ? WHERE uuid = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, session);
		ps.setString(2, getUuid());
		ps.execute();
		ps.close();
		setSession(session);
	}
	
	public boolean isOwner(Connection conn, long userId) throws SQLException{
		boolean isOwner = false;
		
		String sql = "SELECT * FROM module WHERE uuid = ? AND owner_id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, getUuid());
		ps.setLong(2, userId);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			isOwner = true;
		}
		rs.close();
		ps.close();
		return isOwner;
	}
	
	public void modifyModule(Connection conn, Module updatedModule) throws SQLException{
		if(Util.requiresUpdate(getName(), updatedModule.getName())){
			setName(conn, updatedModule.getName());
		}
		if(Util.requiresUpdate(getCode(), updatedModule.getCode())){
			setCode(conn, updatedModule.getCode());
		}
		if(Util.requiresUpdate(getSession(), updatedModule.getSession())){
			setSession(conn, updatedModule.getSession());
		}
	}

	public static ArrayList<Module> getModuleListForUUID(Connection conn,
			String uuid, UserAccess uac) throws Exception {
		ArrayList<Module> list = new ArrayList<Module>();

		String sql = "";
		
		if(!Util.isNotNull(uuid)){
			sql = "SELECT su.*, m.* FROM system_uuid su, module m, user_module um WHERE su.system_uuid = m.uuid";
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				Module m = new Module(rs);
				list.add(m);
			}
			rs.close();
			ps.close();
			return list;
		}

		Resource res = Resource.getResourceForUUID(conn, uuid);

		if (null == res) {
			throw new Exception("Unable to find system resource");
		}

		Status currUserStatus = uac.getUser().getStatus();
		PreparedStatement ps;
		ResultSet rs;

		switch (res.getType()) {
		case USER:

			switch (currUserStatus) {
			case STUDENT:
				if (!uac.getUser().getUuid().equalsIgnoreCase(uuid)) {
					throw new Exception("Unable able to search for this UUID");
				}
				sql = "SELECT su.*, m.* FROM system_uuid su, module m, user_module um WHERE su.system_uuid = m.uuid "
						+ "AND m.module_id = um.module_id AND um.user_id = ?";
				ps = conn.prepareStatement(sql);
				ps.setLong(1, uac.getUserId());
				rs = ps.executeQuery();
				while (rs.next()) {
					Module m = new Module(rs);
					list.add(m);
				}
				rs.close();
				ps.close();
				break;
			case STAFF:
				if (!uac.getUser().getUuid().equalsIgnoreCase(uuid)) {
					throw new Exception("Unable able to search for this UUID");
				}
				sql = "SELECT su.*, m.* FROM system_uuid su, module m WHERE su.system_uuid = m.uuid "
						+ "AND m.owner_id = ?";
				ps = conn.prepareStatement(sql);
				ps.setLong(1, uac.getUserId());
				rs = ps.executeQuery();
				while (rs.next()) {
					Module m = new Module(rs);
					list.add(m);
				}
				rs.close();
				ps.close();

				break;
			case ADMIN:
				sql = "SELECT su.*, m.* FROM system_uuid su, module m, user_module um, user u WHERE su.system_uuid = m.uuid "
						+ "AND m.module_id = um.module_id AND um.user_id = u.user_id AND u.uuid = ?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, uuid);
				rs = ps.executeQuery();
				while(rs.next()){
					Module m = new Module(rs);
					list.add(m);
				}
				rs.close();
				ps.close();
				break;
			default:
				throw new Exception("Unable able to search for this UUID");
			}

			break;
		case MODULE:
			Module mod = getModuleForUUID(conn, uuid);
			list.add(mod);
			break;
		case EXPENSE:
			switch (currUserStatus) {
			case STUDENT:
				sql = "SELECT su.*, m.* FROM system_uuid su, module m, expense e WHERE su.system_uuid = m.uuid AND " +
						"m.uuid = e.module_uuid AND e.uuid = ? AND e.user_uuid = ?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, uuid);
				ps.setString(2, uac.getUser().getUuid());
				rs = ps.executeQuery();
				while(rs.next()){
					Module m = new Module(rs);
					list.add(m);
				}
				rs.close();
				ps.close();
				break;
			case STAFF:
				sql = "SELECT su.*, m.* FROM system_uuid su, module m, expense e WHERE su.system_uuid = m.uuid " +
						"AND m.owner_id = ? AND m.uuid = e.module_uuid AND e.uuid = ?";
				ps = conn.prepareStatement(sql);
				ps.setLong(1, uac.getUserId());
				ps.setString(2, uuid);
				rs = ps.executeQuery();
				while(rs.next()){
					Module m = new Module(rs);
					list.add(m);
				}
				rs.close();
				ps.close();
				break;
			case ADMIN:
				sql = "SELECT su.*, m.* FROM system_uuid su, module m, expense e WHERE su.system_uuid = m.uuid " +
						"AND m.uuid = e.module_uuid AND e.uuid = ?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, uuid);
				rs = ps.executeQuery();
				while(rs.next()){
					Module m = new Module(rs);
					list.add(m);
				}
				rs.close();
				ps.close();
				break;
			default:
				throw new Exception("Unable able to search for this UUID");
			}

			break;
		}

		return list;
	}

	public static Module getModuleForUUID(Connection conn, String uuid)
			throws SQLException {
		Module module = null;
		String sql = "SELECT * FROM system_uuid s, module m WHERE s.system_uuid = ? AND s.system_uuid = m.uuid";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, uuid);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			module = new Module(rs);
		}
		return module;
	}
	
	public static Module getModuleForUser(Connection conn, String moduleUUID, long userId) throws SQLException{
		Module module = null;
		String sql = "SELECT * FROM system_uuid s, module m, user_module um, WHERE s.system_uuid = ? AND s.system_uuid = m.uuid " +
				"AND m.module_id = um.module_id AND um.user_id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, moduleUUID);
		ps.setLong(2, userId);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			module = new Module(rs);
		}
		return module;
	}

	public static Module createModule(Connection conn, Module module,
			long userId) throws Exception {
		String sql = "SELECT * FROM system_uuid s, module m WHERE m.code = ? AND m.session = ? AND s.system_uuid = m.uuid";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, module.getCode());
		ps.setString(2, module.getSession());
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			throw new Exception("This module already exists for this session");
		}
		rs.close();
		ps.close();

		long moduleId = -1;

		sql = "INSERT INTO module (code, session) VALUES (?,?)";
		ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		ps.setString(2, module.getSession());
		rs = ps.executeQuery();
		while (rs.next()) {
			moduleId = rs.getLong(1);
		}
		rs.close();
		ps.close();

		String uuid = Util.generateResourceUUID(ResourceType.MODULE, moduleId,
				userId);

		Resource res = Resource.addResourceToDatabase(conn, uuid,
				module.getName(), userId, ResourceType.MODULE);

		if (null == res) {
			throw new Exception("Fail to create module");
		}

		sql = "UPDATE module SET uuid = ? WHERE moduke_id = ?";
		ps = conn.prepareStatement(sql);
		ps.setString(1, uuid);
		ps.setLong(2, moduleId);

		ps.execute();
		ps.close();

		return getModuleForUUID(conn, uuid);
	}

}
